package hn.cch.framework.util;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.StandardOpenOption;
import java.nio.file.attribute.PosixFilePermission;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Excel工具类
 */
public class ExcelUtil {

    private static final Set<PosixFilePermission> permissions = new HashSet<>();
    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    static { // 777权限
        permissions.add(PosixFilePermission.OWNER_READ); // 设置所有者的读取权限
        permissions.add(PosixFilePermission.OWNER_WRITE); // 设置所有者的写入权限
        permissions.add(PosixFilePermission.OWNER_EXECUTE);// 设置所有者的执行权限
        permissions.add(PosixFilePermission.GROUP_READ);// 设置组的读取权限
        permissions.add(PosixFilePermission.GROUP_WRITE);// 设置组的写入权限
        permissions.add(PosixFilePermission.GROUP_EXECUTE);// 设置组的执行权限
        permissions.add(PosixFilePermission.OTHERS_READ);// 设置其他的读取权限
        permissions.add(PosixFilePermission.OTHERS_WRITE);// 设置其他的写入权限
        permissions.add(PosixFilePermission.OTHERS_EXECUTE);// 设置其他的执行权限
    }

    /**
     * 读取Excel文件中的内容
     */
    public static Workbook read(File excelFile) {
        if (!excelFile.exists()) {// 判断模板文件是否存在
            logger.error("excel file is not exist");
            return null;
        }
        Workbook workbook = null;
        try (InputStream inputStream = Files.newInputStream(excelFile.toPath());){
            // 经过测试发现使用File作为参数后续导出文件时会改变原文件
            // workbook = new XSSFWorkbook(excelFile);
            // 使用InputStream作为参数后续导出文件时不会改变原文件
            workbook = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return workbook;
    }


    /**
     * 将内容保存在Excel文件中，此方法适用任何excel
     * 由于表格样式设置繁琐，建议将表格样式设置好作为模板，
     * 程序读取模板后，填充数据完成后再另存为（不改动模板文件）
     */
    public static void save(File excelFile, String writePath, Map<String, Map<Integer, Map<Integer, Object>>> excelData) {
        // 读取模板
        Workbook workbook = read(excelFile);
        assert workbook != null;
        // 填充数据
        fill(workbook, excelData);
        // 导出文件
        save(workbook, writePath);
    }

    /**
     * 填充数据
     *
     * @param workbook
     * @param excelData
     */
    public static void fill(Workbook workbook, Map<String, Map<Integer, Map<Integer, Object>>> excelData) {
        for (String name : excelData.keySet()) {// 表格
            Sheet sheet = workbook.getSheet(name);
            if (sheet == null) {// 不存在就创建
                sheet = workbook.createSheet(name);
            }
            Map<Integer, Map<Integer, Object>> sheetData = excelData.get(name);
            for (Integer i : sheetData.keySet()) {// 行
                Row row = sheet.getRow(i);
                if (row == null) {// 不存在就创建
                    row = sheet.createRow(i);
                }
                Map<Integer, Object> rowData = sheetData.get(i);
                for (Integer j : rowData.keySet()) {// 单元格
                    Cell cell = row.getCell(j);
                    if (cell == null) {// 不存在就创建
                        cell = row.createCell(j);
                    }
                    Object cellData = rowData.get(j);
                    if (cellData instanceof Boolean) {
                        cell.setCellType(CellType.BOOLEAN);// 布尔
                        cell.setCellValue((Boolean) cellData);
                    } else if (cellData instanceof Number) {
                        cell.setCellType(CellType.NUMERIC);// 数值
                        cell.setCellValue(((Number) cellData).doubleValue());
                    } else {// 默认填充字符类型
                        cell.setCellType(CellType.STRING);// 字符
                        cell.setCellValue(cellData.toString());
                    }
                }
            }
        }
    }

    /**
     * 导出文件
     *
     * @param workbook
     * @param writePath
     */
    public static void save(Workbook workbook, String writePath) {
        OutputStream outputStream = null;
        try {

            File writeFile = new File(writePath);
            // 创建父文件夹
            writeFile.getParentFile().mkdirs();
            // 写入文件
            outputStream = new FileOutputStream(writeFile);
            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            logger.error(e.getMessage());
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    logger.error(e.getMessage());
                }
            }
        }

    }

    /**
     * 使用alibaba的easy-excel根据模板填充特殊结构数据
     * <p>
     * 模板注意
     * 1.用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
     * 2.{} 代表普通变量 {.} 代表是list的变量
     *
     * @param excelFile
     * @param writePath
     * @param sheetName
     * @param headerMap
     * @param excelData
     */
    public static void save(File excelFile, String writePath, String
            sheetName, Map<String, Object> headerMap, List<?> excelData) {
        try {
            ExcelWriter excelWriter = EasyExcel.write(Files.newOutputStream(
                            new File(writePath).toPath(), StandardOpenOption.APPEND))
                    .withTemplate(Files.newInputStream(excelFile.toPath())).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            // 填充数据
            excelWriter.fill(headerMap, writeSheet);// 头部数据
            // 填充新行
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(excelData, fillConfig, writeSheet);// 规则数据
            excelWriter.finish();// 填充完成导出文件
        } catch (Exception e) {
            logger.error(e.getMessage());

        }

    }


    /**
     * 横向填充列表数据
     *
     * @param excelFile
     * @param writePath
     * @param sheetName
     * @param fillOrder true:纵向, false:横向
     * @param excelData
     */
    public static void save(File excelFile, String writePath, String
            sheetName, Boolean fillOrder, List<?> excelData) {
        try {
            ExcelWriter excelWriter = EasyExcel.write(Files.newOutputStream(
                            new File(writePath).toPath(), StandardOpenOption.APPEND))
                    .withTemplate(Files.newInputStream(excelFile.toPath())).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            // 填充数据
            FillConfig fillConfig = null;
            if (fillOrder) {// 填充方向
                fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).build();// 纵向填充
            } else {
                fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();// 横向填充
            }
            excelWriter.fill(excelData, fillConfig, writeSheet);// 规则数据
            excelWriter.finish();// 填充完成导出文件
        } catch (Exception e) {
            logger.error(e.getMessage());

        }
    }

}
